package org.barman.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.barman.exception.PersistenceException;
import org.barman.exception.ValidationException;
import org.barman.pojo.Order;
import org.barman.pojo.OrderItem;
import org.barman.util.DbHelper;

public class OrderDAO implements DAO<Order> {

	@Override
	public void insert(Order order) throws PersistenceException, ValidationException {
		try {
			Connection connection = DbHelper.getConnection();

			String columnString = "(status, created";
			String valueString = "(?, ?";
			if (order.getWaiter() != null) {
				columnString += ", waiter_id";
				valueString += ", ?";
			}
			if (order.getBarman() != null) {
				columnString += ", barman_id";
				valueString += ", ?";
			}
			columnString += ")";
			valueString += ")";

			PreparedStatement preparedStatement = connection.prepareStatement("" + "insert into `order`" + columnString
					+ " values" + valueString, Statement.RETURN_GENERATED_KEYS);

			preparedStatement.setString(1, order.getStatus());
			preparedStatement.setDate(2, order.getCreated());
			if (order.getWaiter() != null) {
				preparedStatement.setInt(3, order.getWaiter().getId());
			}
			if (order.getBarman() != null) {
				preparedStatement.setInt(4, order.getBarman().getId());
			}

			if (preparedStatement.executeUpdate() == 0) {
				throw new PersistenceException("order creation error");
			}

			ResultSet keys = preparedStatement.getGeneratedKeys();

			keys.next();
			int orderId = keys.getInt(1);
			keys.close();
			preparedStatement.close();

			for (OrderItem orderItem : order.getOrderItems()) {
				preparedStatement = connection.prepareStatement(""
						+ "insert into order_has_item(order_id, item_id, quantity)" + " values(?, ?, ?)");
				preparedStatement.setInt(1, orderId);
				preparedStatement.setInt(2, orderItem.getItemId());
				preparedStatement.setInt(3, orderItem.getQuantity());
				if (preparedStatement.executeUpdate() == 0) {
					throw new PersistenceException("order creation error");
				}
				preparedStatement.close();
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("INSERT ORDER ERROR.", e.getMessage(), e);
		}
	}

	@Override
	public void update(Order order) throws PersistenceException, ValidationException {
		if (order.getId() == null) {
			throw new ValidationException("ORDER ID NOT SET");
		}

		try {
			Connection connection = DbHelper.getConnection();

			PreparedStatement preparedStatement = connection
					.prepareStatement("update `order` set status = ?, waiter_id = ?, barman_id = ?, created = ?, modified = ? where id = ?");
			preparedStatement.setString(1, order.getStatus());
			preparedStatement.setInt(2, order.getWaiter().getId());
			preparedStatement.setInt(3, order.getBarman().getId());
			preparedStatement.setDate(4, order.getCreated());
			preparedStatement.setDate(5, order.getModified());
			preparedStatement.setInt(6, order.getId());

			// TODO update order-item references in order_has_item table

			if (preparedStatement.executeUpdate() == 0) {
				throw new PersistenceException("UPDATE ORDER ERROR.");
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("UPDATE ORDER ERROR.", e.getMessage(), e);
		}
	}

	@Override
	public Order get(int orderId) throws PersistenceException, ValidationException {
		try {
			Connection connection = DbHelper.getConnection();

			PreparedStatement preparedStatement = connection
					.prepareStatement("select id, status, barman_id, waiter_id, created, modified from `order` where id = ?");
			preparedStatement.setInt(1, orderId);
			ResultSet resultSet = preparedStatement.executeQuery();

			Order order = new Order();
			while (resultSet.next()) {

				order.setId(resultSet.getInt(1));
				order.setStatus(resultSet.getString(2));

				UserDAO userDAO = new UserDAO();
				order.setBarman(userDAO.get(resultSet.getInt(3)));
				order.setWaiter(userDAO.get(resultSet.getInt(4)));

				order.setCreated(resultSet.getDate(5));
				order.setModified(resultSet.getDate(6));
			}

			return order;

		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("GET ORDER ERROR.", e.getMessage(), e);
		}
	}

	@Override
	public List<Order> getAll() throws PersistenceException, ValidationException {
		try {
			Connection connection = DbHelper.getConnection();

			PreparedStatement preparedStatement = connection
					.prepareStatement("select id, status, barman_id, waiter_id, created, modified from `order`");
			ResultSet resultSet = preparedStatement.executeQuery();

			List<Order> allOrders = new ArrayList<Order>();
			while (resultSet.next()) {

				Order order = new Order();

				order.setId(resultSet.getInt(1));
				order.setStatus(resultSet.getString(2));

				UserDAO userDAO = new UserDAO();
				order.setBarman(userDAO.get(resultSet.getInt(3)));
				order.setWaiter(userDAO.get(resultSet.getInt(4)));

				order.setCreated(resultSet.getDate(5));
				order.setModified(resultSet.getDate(6));

				allOrders.add(order);
			}

			return allOrders;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("GET ALL ORDERS ERROR.", e.getMessage(), e);
		}
	}

	public List<Order> getAllByWaiterId(int waiterId) throws PersistenceException, ValidationException {
		try {
			Connection connection = DbHelper.getConnection();

			PreparedStatement preparedStatement = connection
					.prepareStatement("select id, status, barman_id, waiter_id, created, modified from `order` where waiter_id = ?");
			preparedStatement.setInt(1, waiterId);
			ResultSet resultSet = preparedStatement.executeQuery();

			List<Order> allOrders = new ArrayList<Order>();
			while (resultSet.next()) {

				Order order = new Order();

				order.setId(resultSet.getInt(1));
				order.setStatus(resultSet.getString(2));

				UserDAO userDAO = new UserDAO();
				order.setBarman(userDAO.get(resultSet.getInt(3)));
				order.setWaiter(userDAO.get(resultSet.getInt(4)));

				order.setCreated(resultSet.getDate(5));
				order.setModified(resultSet.getDate(6));

				allOrders.add(order);
			}

			return allOrders;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("GET ALL ORDERS ERROR.", e.getMessage(), e);
		}
	}

	public List<Order> getAllByStatus(String status) throws PersistenceException, ValidationException {
		try {
			Connection connection = DbHelper.getConnection();

			PreparedStatement preparedStatement = connection
					.prepareStatement("select id, status, barman_id, waiter_id, created, modified from `order` where status = ?");
			preparedStatement.setString(1, status);
			ResultSet resultSet = preparedStatement.executeQuery();

			List<Order> allOrders = new ArrayList<Order>();
			while (resultSet.next()) {

				Order order = new Order();

				order.setId(resultSet.getInt(1));
				order.setStatus(resultSet.getString(2));

				UserDAO userDAO = new UserDAO();
				order.setBarman(userDAO.get(resultSet.getInt(3)));
				order.setWaiter(userDAO.get(resultSet.getInt(4)));

				order.setCreated(resultSet.getDate(5));
				order.setModified(resultSet.getDate(6));

				allOrders.add(order);
			}

			return allOrders;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("GET ALL ORDERS ERROR.", e.getMessage(), e);
		}
	}

	public List<Order> getAllByBarmanIdAndStatus(String status, int barmanId) throws PersistenceException,
			ValidationException {
		try {
			Connection connection = DbHelper.getConnection();

			PreparedStatement preparedStatement = connection
					.prepareStatement("select id, status, barman_id, waiter_id, created, modified from `order` where status = ? and barman_id = ?");
			preparedStatement.setString(1, status);
			preparedStatement.setInt(2, barmanId);
			ResultSet resultSet = preparedStatement.executeQuery();

			List<Order> allOrders = new ArrayList<Order>();
			while (resultSet.next()) {

				Order order = new Order();

				order.setId(resultSet.getInt(1));
				order.setStatus(resultSet.getString(2));

				UserDAO userDAO = new UserDAO();
				order.setBarman(userDAO.get(resultSet.getInt(3)));
				order.setWaiter(userDAO.get(resultSet.getInt(4)));

				order.setCreated(resultSet.getDate(5));
				order.setModified(resultSet.getDate(6));

				allOrders.add(order);
			}

			return allOrders;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("GET ALL ORDERS ERROR.", e.getMessage(), e);
		}
	}

	@Override
	public void delete(int orderId) throws PersistenceException, ValidationException {
		try {
			Connection connection = DbHelper.getConnection();

			PreparedStatement preparedStatement = connection.prepareStatement("delete from `order` where id = ?");
			preparedStatement.setInt(1, orderId);

			if (preparedStatement.executeUpdate() == 0) {
				throw new PersistenceException("DELETE ORDER ERROR.");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new PersistenceException("UPDATE ORDER ERROR.", e.getMessage(), e);
		}
	}
}
